USE dba;
GO

EXEC dbo.SP_CheckDist
GO

/**-- find the pending rows to be processed for Log reader
set statistics IO on
set statistics time on
go
select @@VERSION

Use Distribution
select a.article, c.article_id,count(c.article_id) as [number of changes to be Processed]
from MSrepl_commands c, MSarticles a
where c.article_id = a.article_id
group by a.article, c.article_id
order by [number of changes to be Processed] DESCk
go
use RD4_DWCR
go
select top(100) * FROM JDEINItemMaster
where SalesCategory01 <>' '


--select top (100) * from JDEGSUserDefinedCodes
--where
--InstallSystemCode='41' and UserDefinedCode='S1'
select replace(convert(varchar,cast( count(*)  as money),1), '.00','')  from JDEGAAccountLedger



select convert(varchar, cast(1234567 as money) ,1)
select replace(convert(varchar,cast(1234567 as money),1), '.00','')

    USE msdb
    EXEC sp_send_dbmail
      @profile_name = "Farmer",
      @recipients = "ychoi@farmerbros.com",
      @subject = "FBCDATAWH DIST Check",
      
      @execute_query_database = "DBA",
      @query = "exec dbo.SP_CheckDist",
	  @body_format = 'text',
	  @body = "The result dbo.SP_CheckDist is appended below.",
	  @attach_query_result_as_file=0,
	  @query_result_separator="      ",
	  @query_result_width = 9999,
	  @query_result_no_padding=5


select @@servername, @@version 

-- Find TEMPDB contraints.
Select session_id,
    wait_type,
    wait_duration_ms,
    blocking_session_id,
    resource_description,
    ResourceType = Case
        When Cast(Right(resource_description, Len(resource_description)
            - Charindex(':', resource_description, 3)) As Int) - 1 % 8088 = 0 Then 'Is PFS Page'
        When Cast(Right(resource_description, Len(resource_description)
            - Charindex(':', resource_description, 3)) As Int) - 2 % 511232 = 0 Then 'Is GAM Page'
        When Cast(Right(resource_description, Len(resource_description)
            - Charindex(':', resource_description, 3)) As Int) - 3 % 511232 = 0 Then 'Is SGAM Page'
        Else 'Is Not PFS, GAM, or SGAM page'
    End
From sys.dm_os_waiting_tasks
Where wait_type Like 'PAGE%LATCH_%'
And resource_description Like '2:%'

select * FROM Sys.dm_os_waiting_tasks
where resource_description is not null
go





--- what are my TempDB Objects.

USE tempDB; 

WITH Objs (
    ObjectName,
    ObjectID,
    IndexID,
    AU_ID,
    used_pages,
    AU_Type)
AS (SELECT OBJECT_NAME(object_id) AS ObjectName,
        object_id,
        index_id,
        allocation_unit_id,
        used_pages,
        AU.type_desc
    FROM sys.allocation_units AS AU
    INNER JOIN sys.partitions AS P
        ON AU.container_id = P.hobt_id
            -- IN_ROW_DATA and ROW_OVERFLOW_DATA
            AND AU.type In (1, 3)
    UNION ALL
    SELECT OBJECT_NAME(object_id) AS ObjectName,
        object_id,
        index_id,
        allocation_unit_id,
        used_pages,
        AU.type_desc
    FROM sys.allocation_units AS AU
    INNER JOIN sys.partitions AS P
        ON AU.container_id = P.partition_id
            -- LOB_DATA
            AND AU.type = 2
    )
SELECT ObjectName,
    AU_Type,
    IndexID,
    MAX(used_pages) PagesOnDisk,
    COUNT(*) PagesInCache,
    MAX(used_pages) - COUNT(*) PageAllocationDiff
FROM sys.dm_os_buffer_descriptors AS BD
LEFT JOIN Objs O
    ON BD.allocation_unit_id = O.AU_ID
WHERE database_id = DB_ID()
AND ObjectPropertyEx(ObjectID, 'IsUserTable') = 1
GROUP BY ObjectName, AU_Type, IndexID , used_pages
ORDER BY O.ObjectName, O.AU_Type;


DBCC MEMORYSTATUS

ALTER DATABASE DI_Local
MODIFY FILE 
(NAME = 'DI_Local_log', MAXSIZE = UNLIMITED )

SELECT name, physical_name
FROM sys.master_files
WHERE database_id = DB_ID('DI_Local');

Kill 102


select  
    p.*,  
    q.*, 
    cp.plan_handle 
from  
    sys.dm_exec_cached_plans cp 
    cross apply sys.dm_exec_query_plan(cp.plan_handle) p 
    cross apply sys.dm_exec_sql_text(cp.plan_handle) as q 
where  
    cp.cacheobjtype = 'Compiled Plan' and 
    p.query_plan.value('declare namespace  
p="http://schemas.microsoft.com/sqlserver/2004/07/showplan"; 
        max(//p:RelOp/@Parallel)', 'float') > 0
select top 25 
    sql_text.text, 
    sql_handle, 
    plan_generation_num, 
    execution_count, 
    dbid, 
    objectid  
from  
    sys.dm_exec_query_stats a 
    cross apply sys.dm_exec_sql_text(sql_handle) as sql_text 
where  
    plan_generation_num >1 
order by plan_generation_num desc

USE [msdb]
GO

declare @m money
set @m = 123.67
select @m - cast(@m as int)
select cast(@m as int) - @m

-- Object:  StoredProcedure [MS_PerfDashboard].[usp_Main_GetSessionInfo]    Script Date: 06/19/2008 15:35:54 
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
 
ALTER procedure [MS_PerfDashboard].[usp_Main_GetSessionInfo]
as
begin
      select count(*) as num_sessions,
            sum(convert(bigint, s.total_elapsed_time)) as total_elapsed_time,
            sum(convert(bigint, s.cpu_time)) as cpu_time,
            sum(convert(bigint, s.total_elapsed_time)) - sum(convert(bigint, s.cpu_time)) as wait_time,
            --sum(convert(bigint, datediff(ms, login_time, getdate()))) - sum(convert(bigint, s.total_elapsed_time)) as idle_connection_time,
            --FIX to correct for sessions connected >24 days
            sum(convert(bigint, CAST ( DATEDIFF ( minute, login_time, getdate()) AS BIGINT)*60000 + DATEDIFF ( millisecond, DATEADD ( minute, DATEDIFF ( minute, login_time, getdate() ), login_time ),getdate() ))) - sum(convert(bigint, s.total_elapsed_time)) as idle_connection_time,
            case when sum(s.logical_reads) > 0 then (sum(s.logical_reads) - isnull(sum(s.reads), 0)) / convert(float, sum(s.logical_reads))
                  else NULL
                  end as cache_hit_ratio
      from sys.dm_exec_sessions s
      where s.is_user_process = 0x1
end



SELECT
    B.login_name,
    A.client_net_address, B.Session_id,
    NoOfConnections = COUNT(*)
FROM
    sys.dm_exec_connections A
		INNER JOIN sys.dm_exec_sessions B ON
			A.session_id = B.session_id
GROUP BY
    login_name,
    client_net_address,
    B.Session_id
    
use tempdb 
--  In SQL Server 2005, this view is applicable only to the tempdb database. 
select  db_name(database_id) as DbName
      , cast(user_object_reserved_page_count * 8 / 1024 as decimal(9, 2)) as user_object_reserved_MB
      , cast(internal_object_reserved_page_count * 8.0 / 1024 as decimal(9, 2)) as internal_object_reserved_MB
      , cast(version_store_reserved_page_count * 8.0 / 1024 as decimal(9, 2)) as version_store_reserved_MB
      , cast(unallocated_extent_page_count * 8.0 / 1024 as decimal(9, 2)) as unallocated_extent_MB
      , cast(mixed_extent_page_count * 8.0 / 1024 as decimal(9, 2)) as mixed_extent_MB
      , *
from    sys.dm_db_file_space_usage
order by DbName
      , [file_id]


SELECT t1.session_id,
(t1.internal_objects_alloc_page_count + task_alloc) as allocated,
(t1.internal_objects_dealloc_page_count + task_dealloc) as deallocated
        , t3.sql_handle, t3.statement_start_offset
        , t3.statement_end_offset, t3.plan_handle
        , QP.*
from sys.dm_db_session_space_usage as t1
inner join sys.dm_exec_requests t3
on t1.session_id = t3.session_id
inner join (select session_id, 
                           sum(internal_objects_alloc_page_count) as task_alloc,
                           sum (internal_objects_dealloc_page_count) as task_dealloc
                                  from sys.dm_db_task_space_usage group by session_id) as t2
on t1.session_id = t2.session_id 
and t1.session_id >50
and t1.database_id = 2   --- tempdb is database_id=2
CROSS APPLY sys.dm_exec_query_plan(t3.plan_handle) QP

order by allocated DESC



Use Distribution
select a.article, c.article_id,count(c.article_id) as [number of changes to be Processed]
from MSrepl_commands c, MSarticles a
where c.article_id = a.article_id
and a.article_id in (408,409,410, 411,412,413, 414,415)
group by a.article, c.article_id
order by [number of changes to be Processed] DESC

go



DBCC MEMORYSTATUS 


SELECT CONVERT (varchar(30), GETDATE(), 121) as runtime,
DATEADD (ms, -1 * (sys.ms_ticks - a.[Record Time]), GETDATE()) AS Notification_time,  
 a.* , sys.ms_ticks AS [Current Time]
 FROM 
 (SELECT x.value('(//Record/ResourceMonitor/Notification)[1]', 'varchar(30)') AS [Notification_type], 
 x.value('(//Record/MemoryRecord/MemoryUtilization)[1]', 'bigint') AS [MemoryUtilization %], 
 x.value('(//Record/MemoryRecord/TotalPhysicalMemory)[1]', 'bigint') AS [TotalPhysicalMemory_KB], 
 x.value('(//Record/MemoryRecord/AvailablePhysicalMemory)[1]', 'bigint') AS [AvailablePhysicalMemory_KB], 
 x.value('(//Record/MemoryRecord/TotalPageFile)[1]', 'bigint') AS [TotalPageFile_KB], 
 x.value('(//Record/MemoryRecord/AvailablePageFile)[1]', 'bigint') AS [AvailablePageFile_KB], 
 x.value('(//Record/MemoryRecord/TotalVirtualAddressSpace)[1]', 'bigint') AS [TotalVirtualAddressSpace_KB], 
 x.value('(//Record/MemoryRecord/AvailableVirtualAddressSpace)[1]', 'bigint') AS [AvailableVirtualAddressSpace_KB], 
 x.value('(//Record/MemoryNode/@id)[1]', 'bigint') AS [Node Id], 
 x.value('(//Record/MemoryNode/ReservedMemory)[1]', 'bigint') AS [SQL_ReservedMemory_KB], 
 x.value('(//Record/MemoryNode/CommittedMemory)[1]', 'bigint') AS [SQL_CommittedMemory_KB], 
 x.value('(//Record/@id)[1]', 'bigint') AS [Record Id], 
 x.value('(//Record/@type)[1]', 'varchar(30)') AS [Type], 
 x.value('(//Record/ResourceMonitor/Indicators)[1]', 'bigint') AS [Indicators], 
 x.value('(//Record/@time)[1]', 'bigint') AS [Record Time]
 FROM (SELECT CAST (record as xml) FROM sys.dm_os_ring_buffers 
 WHERE ring_buffer_type = 'RING_BUFFER_RESOURCE_MONITOR') AS R(x)) a 
CROSS JOIN sys.dm_os_sys_info sys
ORDER BY a.[Record Time] ASC
Go


select * FROM  sys.dm_os_ring_buffers
GO

-- find Article ID
select Article_ID, article FROM distribution.dbo.msArticles
where article='JDEABAddressBookMaster'


-- Show list of commands for JDEABAddressBookMaster (Ariticle ID 338)
sp_browsereplcmds @article_id = 338

sp_browsereplcmds @article_id =409

exec sp_WhoIsActive
Go

select type, sum(single_page), sum(multi_page)
from sys.dm_os_memory_clerks
group by type
order by sum(single_page+multi_page) desc;



use MicroStrategy_Views
select * from Common_Star_Dates
where FiscalYear = '2010'
and FiscalMonth < '07'
go
-- Show list of commands for JDEOWARLedgger (Ariticle ID 363)
sp_browsereplcmds @article_id = 363

SELECT CONVERT(varchar,GETDATE(),1)
--Return Value = 06/06/07
SELECT CONVERT(varchar,GETDATE(),101)
--Return Value = 06/06/2007
exec sysmail_start_sp
**/
/**
SELECT CONVERT(varchar,GETDATE(),1)
Return Value = 06/06/07
SELECT CONVERT(varchar,GETDATE(),101)
--Return Value = 06/06/2007


select * FROM sys.dm_io_virtual_file_stats(5,NULL)
go
select  db_id() 
go
Select  wait_type, 
        waiting_tasks_count, 
        wait_time_ms
from	sys.dm_os_wait_stats  
where	wait_type like 'PAGEIOLATCH%'  
order by wait_type

select 
    database_id, 
    file_id, 
    io_stall,
    io_pending_ms_ticks,
    scheduler_address 
from	sys.dm_io_virtual_file_stats(NULL, NULL)t1,
        sys.dm_io_pending_io_requests as t2
where	t1.file_handle = t2.io_handle


select top 10
    (total_logical_reads/execution_count) as avg_logical_reads,
    (total_logical_writes/execution_count) as avg_logical_writes,
    (total_physical_reads/execution_count) as avg_phys_reads,
     Execution_count, 
    statement_start_offset as stmt_start_offset, 
    sql_handle, 
    plan_handle
from sys.dm_exec_query_stats  
order by 
 (total_logical_reads + total_logical_writes) Desc
/** Distribution **/
Use Distribution
select * from distribution.dbo.MSarticles 
select * from distribution.dbo.MSarticles where publisher_db = 'RD_DWCR'

select article_id,count(article_id) from MSrepl_commands group by article_id



-- List all the schedulers and look at the number of runnable tasks.
select 
	scheduler_id,
	current_tasks_count,
	runnable_tasks_count
from
	sys.dm_os_schedulers
where
	scheduler_id<255

--SP_who2
--use tempdb
--sp_spaceused
select (size*8) as FileSizeKB from sys.database_files

/**
a high-level view of which currently cached batches or procedures are using the most CPU. 
The query aggregates the CPU consumed by all statements with 
the same plan__handle (meaning that they are part of the same batch or procedure).
**/
select top 50 
    sum(qs.total_worker_time) as total_cpu_time, 
    sum(qs.execution_count) as total_execution_count,
    count(*) as  number_of_statements, 
    qs.plan_handle 
from 
    sys.dm_exec_query_stats qs
group by qs.plan_handle
order by sum(qs.total_worker_time) desc

SELECT 
(Physical_memory_in_bytes/1024.0)/1024.0 AS Physical_memory_in_Mb 
FROM 
sys.dm_os_sys_info

/**
sys.dm_exec_query_stats.
In particular, plan_generation_num indicates the number of times the query has recompiled. 
The following sample query gives you the top 25 stored procedures that have been recompiled.
**/

select * 
from sys.dm_exec_query_optimizer_info

select top 25
    sql_text.text,
    sql_handle,
    plan_generation_num,
    execution_count,
    dbid,
    objectid 
from 
    sys.dm_exec_query_stats a
    cross apply sys.dm_exec_sql_text(sql_handle) as sql_text
where 
    plan_generation_num >1
order by plan_generation_num desc



/**

Inefficient query plans are usually detected comparatively. 
An inefficient query plan may cause increased CPU consumption. 
The query against sys.dm_exec_query_stats is 
an efficient way to determine which query is using the most cumulative CPU. 
**/
select 
    highest_cpu_queries.plan_handle, 
    highest_cpu_queries.total_worker_time,
    q.dbid,
    q.objectid,
    q.number,
    q.encrypted,
    q.[text]
from 
    (select top 50 
        qs.plan_handle, 
        qs.total_worker_time
    from 
        sys.dm_exec_query_stats qs
    order by qs.total_worker_time desc) as highest_cpu_queries
    cross apply sys.dm_exec_sql_text(plan_handle) as q
order by highest_cpu_queries.total_worker_time desc

/**

From a running server, 
you can determine whether any active requests are running in parallel for 
a given session by using the following query.
**/
select 
    r.session_id,
    r.request_id,
    max(isnull(exec_context_id, 0)) as number_of_workers,
    r.sql_handle,
    r.statement_start_offset,
    r.statement_end_offset,
    r.plan_handle
from 
    sys.dm_exec_requests r
    join sys.dm_os_tasks t on r.session_id = t.session_id
    join sys.dm_exec_sessions s on r.session_id = s.session_id
where 
    s.is_user_process = 0x1
group by 
    r.session_id, r.request_id, 
    r.sql_handle, r.plan_handle, 
    r.statement_start_offset, r.statement_end_offset
having max(isnull(exec_context_id, 0)) > 0

--
-- Find query plans that may run in parallel
--
select 
    p.*, 
    q.*,
    cp.plan_handle
from 
    sys.dm_exec_cached_plans cp
    cross apply sys.dm_exec_query_plan(cp.plan_handle) p
    cross apply sys.dm_exec_sql_text(cp.plan_handle) as q
where 
    cp.cacheobjtype = 'Compiled Plan' and
    p.query_plan.value('declare namespace p="http://schemas.microsoft.com/sqlserver/2004/07/showplan";
        max(//p:RelOp/@Parallel)', 'float') > 0
        
select 
    qs.sql_handle, 
    qs.statement_start_offset, 
    qs.statement_end_offset, 
    q.dbid,
    q.objectid,
    q.number,
    q.encrypted,
    q.text
from 
    sys.dm_exec_query_stats qs
    cross apply sys.dm_exec_sql_text(qs.plan_handle) as q
where 
    qs.total_worker_time > qs.total_elapsed_time
--SQL Trace
--Look for the following signs of parallel queries, which could be either 
--statements or batches that have CPU time greater than the duration.

select 
    EventClass, 
    TextData 
from 
    ::fn_trace_gettable('c:\temp\high_cpu_trace.trc', default)
where 
    EventClass in (10, 12)	
    -- RPC:Completed, SQL:BatchCompleted and CPU > Duration/1000	
    -- CPU is in milliseconds, Duration in microseconds Or can be Showplans (un-encoded) that have Parallelism operators in them
select 
    EventClass, 
    TextData 
from 
    ::fn_trace_gettable('c:\temp\high_cpu_trace.trc', default)
where TextData LIKE '%Parallelism%'    

Select  wait_type, 
        waiting_tasks_count, 
        wait_time_ms
from	sys.dm_os_wait_stats  
where	wait_type like 'PAGEIOLATCH%'  
order by wait_type


select 
    database_id, 
    file_id, 
    io_stall,
    io_pending_ms_ticks,
    scheduler_address 
from	sys.dm_io_virtual_file_stats(NULL, NULL)t1,
        sys.dm_io_pending_io_requests as t2
where	t1.file_handle = t2.io_handle


select top 5 
    (total_logical_reads/execution_count) as avg_logical_reads,
    (total_logical_writes/execution_count) as avg_logical_writes,
    (total_physical_reads/execution_count) as avg_phys_reads,
     Execution_count, 
    statement_start_offset as stmt_start_offset, 
    sql_handle, 
    plan_handle
from sys.dm_exec_query_stats  
order by 
 (total_logical_reads + total_logical_writes) Desc



checkpoint
dbcc freeproccache
dbcc dropcleanbuffers


   select text 
from sys.dm_exec_sql_text(0x02000000E0E82429A5F553AD1527034E3019CEAE65AEDDA1)

   select text 
from sys.dm_exec_sql_text(0x020000002379871D39B4179D371A9A5098D938E0058FA525)



---------------------------------------------------------------------
--- Rebuild Indexes 031210
----------------------------------------------

/*Perform a 'USE <database name>' to select the database in which to run the script.*/
-- Declare variables
SET NOCOUNT ON
DECLARE @tablename VARCHAR (128)
DECLARE @execstr   VARCHAR (255)
DECLARE @objectid  INT
DECLARE @indexid   INT
DECLARE @frag      DECIMAL
DECLARE @maxfrag   DECIMAL

-- Decide on the maximum fragmentation to allow
SELECT @maxfrag = 30.0

-- Declare cursor
DECLARE tables CURSOR FOR
   SELECT TABLE_NAME
   FROM INFORMATION_SCHEMA.TABLES
   WHERE TABLE_TYPE = 'BASE TABLE'

-- Create the table
CREATE TABLE #fraglist (
   ObjectName CHAR (255),
   ObjectId INT,
   IndexName CHAR (255),
   IndexId INT,
   Lvl INT,
   CountPages INT,
   CountRows INT,
   MinRecSize INT,
   MaxRecSize INT,
   AvgRecSize INT,
   ForRecCount INT,
   Extents INT,
   ExtentSwitches INT,
   AvgFreeBytes INT,
   AvgPageDensity INT,
   ScanDensity DECIMAL,
   BestCount INT,
   ActualCount INT,
   LogicalFrag DECIMAL,
   ExtentFrag DECIMAL)

-- Open the cursor
OPEN tables

-- Loop through all the tables in the database
FETCH NEXT
   FROM tables
   INTO @tablename

WHILE @@FETCH_STATUS = 0
BEGIN
-- Do the showcontig of all indexes of the table
   INSERT INTO #fraglist 
   EXEC ('DBCC SHOWCONTIG (''' + @tablename + ''') 
      WITH FAST, TABLERESULTS, ALL_INDEXES, NO_INFOMSGS')
   FETCH NEXT
      FROM tables
      INTO @tablename
END

-- Close and deallocate the cursor
CLOSE tables
DEALLOCATE tables

-- Declare cursor for list of indexes to be defragged
DECLARE indexes CURSOR FOR
   SELECT ObjectName, ObjectId, IndexId, LogicalFrag
   FROM #fraglist
   WHERE LogicalFrag >= @maxfrag
      AND INDEXPROPERTY (ObjectId, IndexName, 'IndexDepth') > 0

-- Open the cursor
OPEN indexes

-- loop through the indexes
FETCH NEXT
   FROM indexes
   INTO @tablename, @objectid, @indexid, @frag

WHILE @@FETCH_STATUS = 0
BEGIN
   PRINT 'Executing DBCC INDEXDEFRAG (0, ' + RTRIM(@tablename) + ',
      ' + RTRIM(@indexid) + ') - fragmentation currently '
       + RTRIM(CONVERT(varchar(15),@frag)) + '%'
   SELECT @execstr = 'DBCC INDEXDEFRAG (0, ' + RTRIM(@objectid) + ',
       ' + RTRIM(@indexid) + ')'
   EXEC (@execstr)

   FETCH NEXT
      FROM indexes
      INTO @tablename, @objectid, @indexid, @frag
END

-- Close and deallocate the cursor
--CLOSE indexes
--DEALLOCATE indexes

-- Delete the temporary table
--DROP TABLE #fraglist
--GO



**/
